<?php
require_once dirname(__FILE__) . '/../common/common.inc.php';
require_once dirname(__FILE__) . "/../database/$database/database.inc.php";

header('Content-Type: text/plain');

try {
  $hostname = DB_HOST;
  $username = DB_LOGIN;
  $password = DB_PASS;
  $dbname = DB_NAME;
  $myPdo = new PDO ('mysql:host=' . $hostname, $username, $password);
  $myPdo->exec('USE ' . $dbname);
  $myPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
  echo ERROR_SIGN . $e->getMessage();
}

$action = MiscUtils::getParam('action', NULL);
try {
  $myPdo->beginTransaction();
  switch ($action) {
    case 'login':
      login($myPdo);
      break;
    case 'client':
      client($myPdo);
      break;
    case 'clients':
      clients($myPdo);
      break;
    case 'visa':
      visa($myPdo);
      break;
    case 'visas':
      visas($myPdo);
      break;
    case 'schools':
      schools($myPdo);
      break;
    case 'remind':
      remind($myPdo);
      break;
    case 'delete':
      delete($myPdo);
      break;
    case 'countIt':
      countIt($myPdo);
      break;
    default:
      break;
  }
  $myPdo->commit();
} catch (PDOException $e) {
  echo ERROR_SIGN . $e->getMessage();
}

function login($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  
  $result = MiscUtils::db(getMyPdo(), '*', 'pz_user', NULL, "t.account = '$q->account' AND t.password = '$q->password'", NULL, NULL, NULL, NULL);
  if (count($result->data) == 1) {
    $user = $result->data[0];
    
    $_SESSION[USER] = $user;
    setcookie(USER, json_encode($_SESSION[USER]), (time() + 3600), PATH, DOMAIN);
    
    echo json_encode($user);
  } else {
    echo ERROR_SIGN . 'Incorrect Account/Password';
  }
}

function client($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  
  $result = new stdClass();
  foreach ($q as &$tmp) {
    $orm = classToOrm($tmp->n);
    if ($orm) {
      foreach ($tmp->a as &$obj) {
        if (isset($obj->id)) {
          $myPdo->exec(MiscUtils::obj2Upd($tmp->n, $obj));
          $result->id = $obj->id;
        } else {
          $myPdo->exec(MiscUtils::obj2Ins($tmp->n, $obj));
          $result->id = $myPdo->lastInsertId();
        }
      }
      foreach ($tmp->r as &$obj) {
        $myPdo->exec(MiscUtils::obj2Del($tmp->n, $obj));
        //...
      }
    }
  }
  echo json_encode($result);
}

function clients($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  
  $cond = '';
  if (isset($q->s->search_client_name) && $q->s->search_client_name != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.name_family LIKE '%" . $q->s->search_client_name . "%'";
  }
  if (isset($q->s->search_given_name) && $q->s->search_given_name != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.name_first LIKE '%" . $q->s->search_given_name . "%'";
  }
  if (isset($q->s->search_date_birth) && $q->s->search_date_birth != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.date_birth = '" . $q->s->search_date_birth . "'";
  }
  if (isset($q->s->search_nationality) && $q->s->search_nationality != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.nationality LIKE '%" . $q->s->search_nationality . "%'";
  }
  if (isset($q->s->search_phone) && $q->s->search_phone != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "(t.phone1 LIKE '%" . $q->s->search_phone . "%' OR t.phone2 LIKE '%" . $q->s->search_phone . "%' OR t.phone3 LIKE '%" . $q->s->search_phone . "%')";
  }
  if (isset($q->s->search_school) && $q->s->search_school != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t3.school_name LIKE '%" . $q->s->search_school . "%'";
  }
  if (isset($q->s->search_staff) && $q->s->search_staff != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.staff LIKE '%" . $q->s->search_staff . "%'";
  }
  if (isset($q->s->search_agent) && $q->s->search_agent != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.agent LIKE '%" . $q->s->search_agent . "%'";
  }
  if (isset($q->s->search_visa_client_num) && $q->s->search_visa_client_num != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t2.client_num LIKE '%" . $q->s->search_visa_client_num . "%'";
  }
  if (isset($q->s->search_visa_app_num) && $q->s->search_visa_app_num != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t2.visa_appl_num LIKE '%" . $q->s->search_visa_app_num . "%'";
  }
  if (isset($q->s->search_visa_type) && $q->s->search_visa_type != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t2.visa_appl_type LIKE '%" . $q->s->search_visa_type . "%'";
  }
  if (isset($q->s->search_submit_from) && $q->s->search_submit_from != 0) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t2.visa_appl_date_submit >= '" . $q->s->search_submit_from . "'";
  }
  if (isset($q->s->search_submit_to) && $q->s->search_submit_to != 0) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t2.visa_appl_date_submit <= '" . $q->s->search_submit_to . "'";
  }
  
  if (isset($q->s->search_pass_num) && $q->s->search_pass_num != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.pass_num LIKE '%" . $q->s->search_pass_num . "%'";
  }
  if (isset($q->s->search_date_birth) && $q->s->search_date_birth != 0) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.date_birth = '" . $q->s->search_date_birth . "'";
  }
  if ($_SESSION[USER]->type != USER_TYPE_ADMIN) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= 't.staff = \'' . $_SESSION[USER]->account . '\'';
  }
  $size = (isset($q->size)) ? $q->size : SIZE;
  $result = MiscUtils::db(getMyPdo(), 'DISTINCT t.id, t.*, t1.visa_date_expiry_new', 'pz_client', array(array('pz_visa', 't.latest_visa_id = t1.id'), array('pz_visa', 't.id = t2.client_id'), array('pz_school', 't.id = t3.client_id')), ($cond == '') ? NULL : $cond, $q->o, $q->q, $q->p, $size);
  echo json_encode($result);
}

function visa($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  
  foreach ($q as &$tmp) {
    $orm = classToOrm($tmp->n);
    if ($orm) {
      foreach ($tmp->a as &$obj) {
        if (isset($obj->id)) {
          $myPdo->exec(MiscUtils::obj2Upd($tmp->n, $obj));
        } else {
          $myPdo->exec(MiscUtils::obj2Ins($tmp->n, $obj));
          //...
          $latest_visa_id = $myPdo->lastInsertId();
          $myPdo->exec("UPDATE `pz_client` SET `latest_visa_id` = '$latest_visa_id' WHERE `id` = '$obj->client_id'");
        }
      }
      foreach ($tmp->r as &$obj) {
        $myPdo->exec(MiscUtils::obj2Del($tmp->n, $obj));
        //...
        $latest_visa_id = 0;
        $visas = MiscUtils::db($myPdo, '*', 'pz_visa', NULL, "client_id = $obj->client_id", 't.id', 'DESC', 1, 1)->data;
        if (count($visas) == 1) {
          $latest_visa_id = $visas[0]->id;
        }
        $myPdo->exec("UPDATE `pz_client` SET `latest_visa_id` = '$latest_visa_id' WHERE `id` = '$obj->client_id'");
      }
    }
  }
  echo json_encode(array());
}

function visas($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  
  $cond = '';
  if (isset($q->s->search_client_name) && $q->s->search_client_name != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t1.name_family LIKE '%" . $q->s->search_client_name . "%'";
  }
  if (isset($q->s->search_given_name) && $q->s->search_given_name != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t1.name_first LIKE '%" . $q->s->search_given_name . "%'";
  }
  if (isset($q->s->search_nationality) && $q->s->search_nationality != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t1.nationality LIKE '%" . $q->s->search_nationality . "%'";
  }
  if (isset($q->s->search_phone) && $q->s->search_phone != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "(t1.phone1 LIKE '%" . $q->s->search_phone . "%' OR t1.phone2 LIKE '%" . $q->s->search_phone . "%' OR t1.phone3 LIKE '%" . $q->s->search_phone . "%')";
  }
  if (isset($q->s->search_school) && $q->s->search_school != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t2.school_name LIKE '%" . $q->s->search_school . "%'";
  }
  if (isset($q->s->search_staff) && $q->s->search_staff != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t1.staff LIKE '%" . $q->s->search_staff . "%'";
  }
  if (isset($q->s->search_agent) && $q->s->search_agent != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t1.agent LIKE '%" . $q->s->search_agent . "%'";
  }
  if (isset($q->s->search_visa_client_num) && $q->s->search_visa_client_num != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.client_num LIKE '%" . $q->s->search_visa_client_num . "%'";
  }
  if (isset($q->s->search_visa_app_num) && $q->s->search_visa_app_num != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.visa_appl_num LIKE '%" . $q->s->search_visa_app_num . "%'";
  }
  if (isset($q->s->search_visa_type) && $q->s->search_visa_type != '') {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.visa_appl_type LIKE '%" . $q->s->search_visa_type . "%'";
  }
  if (isset($q->s->search_submit_from) && $q->s->search_submit_from != 0) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.visa_appl_date_submit >= '" . $q->s->search_submit_from . "'";
  }
  if (isset($q->s->search_submit_to) && $q->s->search_submit_to != 0) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= "t.visa_appl_date_submit <= '" . $q->s->search_submit_to . "'";
  }
  
  if (isset($q->s->search_pending) && isset($q->s->search_issued) && isset($q->s->search_declined)) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    if ($q->s->search_pending == 1) {
      $cond .= "(t.visa_result = ''";
    } else {
      $cond .= "(t.visa_result != ''";
    }
    
    if ($q->s->search_issued == 1) {
      $cond .= ($cond == '') ? $cond : ' OR ';
      $cond .= "t.visa_result = '" . VISA_RESULT_ISSUE . "'";
    } else {
      $cond .= ($cond == '') ? $cond : ' AND ';
      $cond .= "t.visa_result != '" . VISA_RESULT_ISSUE . "'";
    }
    
    if ($q->s->search_declined == 1) {
      $cond .= ($cond == '') ? $cond : ' OR ';
      $cond .= "t.visa_result = '" . VISA_RESULT_DECLINE . "')";
    } else {
      $cond .= ($cond == '') ? $cond : ' AND ';
      $cond .= "t.visa_result != '" . VISA_RESULT_DECLINE . "')";
    }
  }
  
  if (isset($q->s->due) && $q->s->due == true) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= 't.date_reminder != -1 AND t.date_reminder <= ' . time() . ' AND t.visa_date_expiry_new != 0 AND t.visa_date_expiry_new <= ' . (time() + 86400 * 60);
  }
  
  if ($_SESSION[USER]->type != USER_TYPE_ADMIN) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= 't1.staff = \'' . $_SESSION[USER]->account . '\'';
  }
  
  $result = MiscUtils::db(getMyPdo(), 'DISTINCT t.id, t.*, t1.name_first, t1.name_family, t1.date_birth, t1.pass_num, t1.staff, t1.agent, t1.phone1, t1.phone2, t1.phone3, t1.email', 'pz_visa', array(array('pz_client', 't.client_id = t1.id'), array('pz_school', 't.client_id = t2.client_id')), ($cond == '') ? NULL : $cond, $q->o, $q->q, $q->p, SIZE);
  echo json_encode($result);
}

function schools($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  
  $cond = '';
  if (isset($q->s->due) && $q->s->due == true) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= 't.date_reminder != -1 AND t.date_reminder <= ' . time() . ' AND t.next_payment_date != 0 AND t.next_payment_date <= ' . (time() + 86400 * 60);
  }
  
  if ($_SESSION[USER]->type != USER_TYPE_ADMIN) {
    $cond .= ($cond == '') ? $cond : ' AND ';
    $cond .= 't1.staff = \'' . $_SESSION[USER]->account . '\'';
  }
  
  $result = MiscUtils::db(getMyPdo(), 'DISTINCT t.id, t.*, t1.name_first, t1.name_family, t1.date_birth, t1.pass_num, t1.staff, t1.agent, t1.phone1, t1.phone2, t1.phone3, t1.email', 'pz_school', array(array('pz_client', 't.client_id = t1.id')), ($cond == '') ? NULL : $cond, $q->o, $q->q, $q->p, SIZE);
  echo json_encode($result);
}

function remind($myPdo) {
	$q = json_decode(MiscUtils::getParam('q', '[]'));
	
  if ($q && isset($q->m) && isset($q->o) && isset($q->t)) {
    require_once dirname(__FILE__) . '/../library/phpMailer/class.phpmailer.php';
    $mailer = new PHPMailer();
    $mailer->IsSMTP();
    $mailer->Host = MAIL_HOST;
    if (MAIL_SMTP) {
      $mailer->SMTPAuth = MAIL_SMTP;
    }
    $mailer->Username = MAIL_USER;
    $mailer->Password = MAIL_PASSWORD;
    $mailer->From = MAIL_FROM;
    $mailer->FromName = MAIL_NAME;
    $mailer->Body = $q->m->content;
    $mailer->Subject = $q->m->subject;
    if ($q->o->email != '') {
      $mailer->AddAddress($q->o->email);
    }
    if (!$mailer->Send()) {
      echo ERROR_SIGN . $mailer->ErrorInfo;
      return;
    }
    $myPdo->exec("UPDATE `$q->t` SET `date_reminder` = '-1' WHERE `id` = '" . $q->o->id . "'");
  }
  echo json_encode(array());
}

function delete($myPdo) {
  $q = json_decode(MiscUtils::getParam('q', '[]'));
  $files = $q->files;

  foreach ($files as &$file) {
    unlink('../' . $file->link);
    $myPdo->exec(MiscUtils::obj2Del('pz_file', $file));
  }
  echo json_encode(array());
}

function countIt($myPdo) {
	$cond = '';
  if ($_SESSION[USER]->type != USER_TYPE_ADMIN) {
    $cond .= ' AND t1.staff = \'' . $_SESSION[USER]->account . '\'';
  }
	$result = new stdClass();
  $tmp = MiscUtils::db(getMyPdo(), 'COUNT(*) AS count', 'pz_visa', array(array('pz_client', 't.client_id = t1.id')), 't.date_reminder != -1 AND t.date_reminder <= ' . time() . ' AND t.visa_date_expiry_new != 0 AND t.visa_date_expiry_new <= ' . (time() + 86400 * 60) . $cond, NULL, NULL, START, INFINITE);
	$result->expiring_visa = $tmp->data[0]->count;
	$tmp = MiscUtils::db(getMyPdo(), 'COUNT(*) AS count', 'pz_school', array(array('pz_client', 't.client_id = t1.id')), 't.date_reminder != -1 AND t.date_reminder <= ' . time() . ' AND t.next_payment_date != 0 AND t.next_payment_date <= ' . (time() + 86400 * 60) . $cond, NULL, NULL, START, INFINITE);
  $result->next_school_payment = $tmp->data[0]->count;
  echo json_encode($result);
}

function getMyPdo() {
  try {
    $host = DB_HOST;
    $login = DB_LOGIN;
    $pass = DB_PASS;
    $db = DB_NAME;
    $myPdo = new PDO ("mysql:host=$host", $login, $pass);
    $myPdo->exec("USE $db");
    $myPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    return $myPdo;
  } catch (PDOException $e) {
    echo ERROR_SIGN . $e->getMessage();
  }
  return NULL;
}
?>